package com.webservice.store;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import com.webservice.bean.QueryResult;
import com.webservice.bean.Student;
import com.webservice.bean.StudentPost;
import com.webservice.bean.Teacher;

public class DataAdapter 
{
	public static String PostedStudentID;
	public static String StudentQuestion;
	public static String StudentQuestionType;
	public static String StudentPostedTeacherID;
	
	private static Map<String,Student> studentValue;
	private static Map<String,Teacher> dbQueryValue;
	private static Map<String,StudentPost> dbStudentPostValue;
	private static Map<String,QueryResult> dbQueryResult;
	private static DataAdapter instanceAdapter=null;
		
	public DataAdapter(String ID,String UserType)
	{
		dbQueryValue=new HashMap<String,Teacher>();
		studentValue=new HashMap<String,Student>();
		dbStudentPostValue=new HashMap<String,StudentPost>();
		dbQueryResult=new HashMap<String,QueryResult>();
		
		if(UserType.equals("Teacher"))
		selectTeacher(ID);
		else if(UserType.equals("Student"))
		selectStudent(ID);	
		else if(UserType.equals("Post"))
		SelectStudentPost(ID);
		else if(UserType.equals("ViewStudentPostedQuery"));
		addStudentPost();
		
	}
	
	public static Map<String, QueryResult> getStudentQueriesStatus(String UserType)
	{ 		
		if(instanceAdapter!=null)
		{
			instanceAdapter = null;
		}
		if(instanceAdapter==null)
		{
			instanceAdapter =  new DataAdapter(null, );
		}
		return dbQueryResult;
	}	

	
  public static Map<String,QueryResult> getStudentPostStatus(String TeacherID,String UserType)
	{
		if(instanceAdapter!=null)
		{
			instanceAdapter=null;
		}
		else if(instanceAdapter==null)
		{
			instanceAdapter=new DataAdapter(TeacherID, UserType);
		}
		return dbQueryResult;
	}
	
	public static Map<String,StudentPost> getStudentPostDetail(String StudentPostID,String UserType)
	{
		if(instanceAdapter!=null)
		{
			instanceAdapter=null;
		}
		else if(instanceAdapter==null)
		{
			instanceAdapter=new DataAdapter(StudentPostID,UserType);
		}
		
		return dbStudentPostValue;		
	}
	
	public static Map<String,Teacher> getTeacherDetail(String TeacherID,String UserType)
	{
		if(instanceAdapter!=null)
		{
			instanceAdapter=null;
		}
		else if(instanceAdapter==null)
		{
			instanceAdapter=new DataAdapter(TeacherID,UserType);
		}		
		
		return dbQueryValue;
	}
	
	public static Map<String,Student> getStudentDetail(String StudentID,String UserType)
	{	
		if(instanceAdapter!=null)
		{
			instanceAdapter=null;
		}
		else if(instanceAdapter==null)
		{
			instanceAdapter=new DataAdapter(StudentID,UserType);
		}
		return studentValue;
	}
	
	public void addStudentPost()
	{
		  System.out.println("MySQL Connect Example.");		    
		  Connection conn = null;		  
		  Statement stmtCheck = null;		  
		  PreparedStatement pstmt = null;	
		  ResultSet rs = null;		  
		  String Status = null;
		  String Description = null;
		  
		  try 
		  {
			  conn = Dbconnector.getConnection();				  			  
		      String qry="Select Teacher_Id from teacher  where Teacher_Id ='"+StudentPostedTeacherID+"' ";
		      stmtCheck = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		      rs = stmtCheck.executeQuery(qry);	
		      rs.last();
		      int i = rs.getRow();
		      rs.beforeFirst();
		      if(i==0)
		      {
		    	  	Status = "false" ;
		    	  	Description = "Teacher ID Doesn't exist";		      		
		      }	
		      else if(i>0)
		      {		    	  
		    	  String query = "insert into student_queries(Student_Id,Question,Option_Type,Teacher_Id) values(?,?,?,?)";			  
				  pstmt = conn.prepareStatement(query); 
				  pstmt.setString(1, PostedStudentID);
				  pstmt.setString(2, StudentQuestion);
				  pstmt.setString(3, StudentQuestionType);
				  pstmt.setString(4, StudentPostedTeacherID);
				  int ii=pstmt.executeUpdate();
				  Status = "true";
				  Description="Successfully Posted";
		  	  }			 

	      	
		    QueryResult value = new QueryResult(Status,Description);
      		dbQueryResult.put("",value);
		  	
		  	if(rs!=null)
		  	{
			  	rs.close();
			}		  	
		  	if(stmtCheck!=null)
		  	{
		  		stmtCheck.close();
		  	}
		  	if(pstmt!=null)
		  	{
		  		pstmt.close();
			}
		  	if(conn!=null)
		  	{
		  		conn.close();
		  	}
		  	System.out.println("Disconnected from insert user");
		  }
		  catch (Exception e) 
		  {
			  e.printStackTrace();
		  }

	}
	
	public void SelectStudentPost(String Teacher_ID)
	{
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;			  
		StudentPost qb = null;
		ArrayList<StudentPost> Listbean = new ArrayList<StudentPost>();
		try 
		{
			  conn = Dbconnector.getConnection();
			  String query = "SELECT Student_Queries_ID,Student_Id, Question,seession_Id FROM student_queries where Teacher_Id='"+Teacher_ID+"'";
		      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		      rs = stmt.executeQuery(query);
		      rs.last();
		      int i = rs.getRow();
		      System.out.println("rowCount=" + i);
		      rs.beforeFirst();
		      qb = new StudentPost();
		      if(i>0)
		      {
		    	  qb.setStatus("true");
		    	  while(rs.next())
		    	  {		      					      					      			
		      			if(rs.getString("Student_Queries_ID")!=null)
			  			{
			  				qb.setPostID(rs.getString("Student_Queries_ID"));
			  			}	
			  			if(rs.getString("Student_Id")!=null)
			  			{
			  				qb.setStudentID(rs.getString("Student_Id"));
			  			}			  			  			
			  			if(rs.getString("Question")!=null)
			  			{
			  				qb.setQuestion(rs.getString("Question"));
			  			}	 
			  			if(rs.getString("Teacher_Id")!=null)
			  			{
			  				qb.setTeacherID(rs.getString("Teacher_Id"));
			  			}			  			
		  			}			      					      		
		      }
		      else
		      {
		    	    qb.setStatus("false");
		    	    qb.setErrorDesc("Student not register with us");
		      }	      
		      
			  Listbean.add(qb);			      
			  System.out.println("List Size "+Listbean.size());
			  if(Listbean!=null )
			  {	  	
				  	StudentPost value = new StudentPost(Listbean);
				  	dbStudentPostValue.put("0",value);			  		
			  }	
	
			  if(rs!=null)
			  {
			  		rs.close();
			  }
			  if(stmt!=null)
			  {
			  		stmt.close();
			  }				  	
			  if(conn!=null)
			  {
			  		conn.close();
			  }
			  System.out.println("Disconnected from database");
		}
		catch(Exception e)
		{
			System.out.println("expo="+e.getLocalizedMessage());			
		}

		
	}
	
	public void selectStudent(String StudentID)
	{	
			
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;			  
		Student qb = null;
		ArrayList<Student> Listbean = new ArrayList<Student>();
		try 
		{
			  conn = Dbconnector.getConnection();
			  String query = "SELECT Student_ID,Student_Name, College_Name, Course FROM student where Student_ID='"+StudentID+"'";
		      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		      rs = stmt.executeQuery(query);
		      rs.last();
		      int i = rs.getRow();
		      System.out.println("rowCount=" + i);
		      rs.beforeFirst();
		      qb = new Student();
		      if(i>0)
		      {
		    	  qb.setStatus("true");
		    	  while(rs.next())
		    	  {		      					      					      			
		      			if(rs.getString("Student_ID")!=null)
			  			{
			  				qb.setStudentID(rs.getString("Student_ID"));
			  			}	
			  			if(rs.getString("Student_Name")!=null)
			  			{
			  				qb.setStudentName(rs.getString("Student_Name"));
			  			}			  			  			
			  			if(rs.getString("College_Name")!=null)
			  			{
			  				qb.setCollageName(rs.getString("College_Name"));
			  			}	 
			  			if(rs.getString("Course")!=null)
			  			{
			  				qb.setCourse(rs.getString("Course"));
			  			}			  			
		  			}			      					      		
		      }
		      else
		      {
		    	    qb.setStatus("false");
		    	    qb.setErrorDesc("Student not register with us");
		      }	      
		      
			  Listbean.add(qb);			      
			  System.out.println("List Size "+Listbean.size());

			  if(Listbean!=null )
			  {	  	
				  	Student value = new Student(Listbean);
				  	studentValue.put("0",value);			  		
			  }	
	
			  if(rs!=null)
			  {
			  		rs.close();
			  }
			  if(stmt!=null)
			  {
			  		stmt.close();
			  }				  	
			  if(conn!=null)
			  {
			  		conn.close();
			  }
			  System.out.println("Disconnected from database");
		}
		catch(Exception e)
		{
			System.out.println("expo="+e.getLocalizedMessage());			
		}
		
	}
	
	public void selectTeacher(String TeacherID)
	{
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;			  
			Teacher qb = null;
			ArrayList<Teacher> Listbean = new ArrayList<Teacher>();
			try 
			{
				  conn = Dbconnector.getConnection();
				  String query = "SELECT Teacher_Id,Teacher_Name, College_Name, Qualification, Designation FROM teacher where Teacher_Id='"+TeacherID+"'";
			      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			      rs = stmt.executeQuery(query);
			      rs.last();
			      int i = rs.getRow();
			      System.out.println("rowCount=" + i);
			      rs.beforeFirst();
			      qb = new Teacher();
			      if(i>0)
			      {
			    	  qb.setStatus("true");
			    	  while(rs.next())
			    	  {		      					      					      			
			      			if(rs.getString("Teacher_Id")!=null)
				  			{
				  				qb.setTeacherID(rs.getString("Teacher_Id"));
				  			}	
				  			if(rs.getString("Teacher_Name")!=null)
				  			{
				  				qb.setTeacherName(rs.getString("Teacher_Name"));
				  			}			  			  			
				  			if(rs.getString("College_Name")!=null)
				  			{
				  				qb.setCollege_Name(rs.getString("College_Name"));
				  			}	 
				  			if(rs.getString("Qualification")!=null)
				  			{
				  				qb.setQualification(rs.getString("Qualification"));
				  			}
				  			if(rs.getString("Designation")!=null)
				  			{
				  				qb.setDesignation(rs.getString("Designation"));
				  			}
			  			}			      					      		
			      }
			      else
			      {
			    	    qb.setStatus("false");
			    	    qb.setErrorDesc("Teacher not register with us");
			      }	      
			      
				  Listbean.add(qb);			      
				  System.out.println("List Size "+Listbean.size());
				  if(Listbean!=null )
				  {	  	
					  	Teacher value = new Teacher(Listbean);
				  		dbQueryValue.put("0",value);			  		
				  }	
		
				  if(rs!=null)
				  {
				  		rs.close();
				  }
				  if(stmt!=null)
				  {
				  		stmt.close();
				  }				  	
				  if(conn!=null)
				  {
				  		conn.close();
				  }
				  System.out.println("Disconnected from database");		
			}
			catch(Exception e)
			{
				System.out.println("expo="+e.getLocalizedMessage());
			}
	}

}
